package com.erdos.common.util;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * for formatting the Excel cell data.
 * @author sunf
 * @since 5/16/2007
 */
public class ExcelFormat {
	public static final String DEFAULT_PERCEN_FORMAT="0.00%";
	public static final String DEFAULT_COMMA_FORMAT="#,##0.00_);(#,##0.00)";
	public static final String DEFAULT_STRING_FORMAT="@";
	public static final String DEFAULT_INT_FORMAT="#,##0_);(#,##0)";
	public static final String DEFAULT_DOUBLE_FORMAT="###.00";
	
	private Map formatStyleMap=new HashMap();
	
	private HSSFDataFormat dataFormat;
	private HSSFWorkbook wb;
	public ExcelFormat(HSSFWorkbook wb) {
		this.dataFormat=wb.createDataFormat();
		this.wb=wb;
	}
	
	/**
	 * formats a cell value to percent format.
	 * eg. 1234.56789
	 * when decimal sets to 0, then the formatted value is 123457%;
	 * if decimal sets to 2, then result is 123456.79%.
	 * @param cell to be formatted HSSFCell
	 * @param decimal decimal digits
	 */
	public void setPercentFormat(HSSFCell cell, int decimal) {
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);		
		
		short fmtIdx=0;
		if(decimal==0) {
			fmtIdx=HSSFDataFormat.getBuiltinFormat("0%");	
		} else if (decimal==2) {
			fmtIdx=HSSFDataFormat.getBuiltinFormat("0.00%");	
		} else {
			fmtIdx=this.dataFormat.getFormat("0." + this.getZeroFilledString(decimal) + "%");			
		}
			
		this.setCellFormat(cell, fmtIdx);
	}
	
	/**
	 * default decimal digits sets to 2.
	 * @param cell to be formatted HSSFCell
	 */
	public void setPercentFormat(HSSFCell cell) {
		this.setPercentFormat(cell, 2);
	}
	
	/**
	 * formats the number with the comma, e.g: 123456.789,
	 * will be formatted as 123,456.789 if decimal is set to 3. 
	 * @param cell to be formatted HSSFCell
	 * @param decimal decimal digits
	 */
	public void setCommaNumber(HSSFCell cell, int decimal) {
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		short fmtIdx=0;
		if(decimal==0) {
			fmtIdx=HSSFDataFormat.getBuiltinFormat("#,##0");	
		} else if(decimal==2) {
			fmtIdx=HSSFDataFormat.getBuiltinFormat("#,##0.00");	
		} else {
			fmtIdx=this.dataFormat.getFormat("#,##0." + this.getZeroFilledString(decimal));	
		}
		this.setCellFormat(cell, fmtIdx);	
	}
	
	/**
	 * default decimal digits sets to 2.
	 * @param cell to be formatted HSSFCell
	 */
	public void setCommaNumber(HSSFCell cell) {
		this.setCommaNumber(cell, 2);
	}	
	
	public void setCellNumberFormat(HSSFCell cell, String format) {
		if(cell==null || HSSFCell.CELL_TYPE_NUMERIC !=cell.getCellType()) return;	
		
		short fmtIdx=this.dataFormat.getFormat(format);
//		System.out.println(format + ", idx=" + fmtIdx + "," +cell);
		this.setCellFormat(cell, fmtIdx);
	}
	
	private String getZeroFilledString(int len) {
		if(len<0) {
			len=Math.abs(len);
		}
		char [] suffix=new char[len];
		Arrays.fill(suffix, '0');
		return String.valueOf(suffix);
	}
	
	private void setCellFormat(HSSFCell cell, short fmtIdx) {
		HSSFCellStyle style=this.getCellStyle(cell, fmtIdx);
		
		style.setDataFormat(fmtIdx);	
		cell.setCellStyle(style);
	}
	
	private HSSFCellStyle getCellStyle(HSSFCell cell, short fmtIdx) {
		Short key=new Short(fmtIdx);
		HSSFCellStyle style=null;
		if(formatStyleMap.containsKey(key)) {
			style= (HSSFCellStyle)formatStyleMap.get(key);
		} else {
			style=this.wb.createCellStyle();
			
			HSSFCellStyle defaultStyle=cell.getCellStyle();
			style.setAlignment(defaultStyle.getAlignment());
			style.setBorderBottom(defaultStyle.getBorderBottom());
			style.setBorderLeft(defaultStyle.getBorderLeft());
			style.setBorderRight(defaultStyle.getBorderRight());
			style.setBorderTop(defaultStyle.getBorderTop());
			style.setBottomBorderColor(defaultStyle.getBottomBorderColor());
			
			formatStyleMap.put(key, style);
		}
		return style;
	}
}
